// 加载Express模块
const express = require('express');

// 加载MySQL模块
const mysql = require('mysql');

// 创建MySQL连接池
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  password: '',        //数据库用户密码
  database: 'lj',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8'      //数据库服务器的编码方式
});

// 创建服务器对象
const server = express();

server.use(express.urlencoded({
  extended: false
}));

//7.静态托管
server.use(express.static('../linkhome-web-project/public'))

// 加载CORS模块
const cors = require('cors');

// 使用CORS中间件
server.use(cors({
  origin: ['http://localhost:8080', 'http://127.0.0.1:8080']
}));

//用户注册接口
server.post('/register', (req, res) => {
  // 获取用户名和密码信息
  let phone = req.body.phone;
  let upwd = req.body.upwd;
  let repwd = req.body.repwd;
  let idCode = req.body.idCode
  //以username为条件进行查找操作，以保证用户名的唯一性
  let sql = 'SELECT COUNT(uid) AS count FROM lj_user WHERE phone=?';
  pool.query(sql, [phone], (error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = 'INSERT lj_user(phone,upwd,repwd,idCode) VALUES(?,?,?,?)';
      pool.query(sql, [phone,upwd,repwd,idCode], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 });
      })
    } else {
      res.send({ message: 'user exists', code: 201 });
    }
  });
});

// 用户登录接口
server.post('/login', (req, res) => {
  //获取用户名和密码信息
  let phone = req.body.phone;
  let upwd = req.body.upwd;
  // SQL语句
  let sql = 'SELECT uid,phone,upwd FROM lj_user WHERE phone=? AND upwd=?';
  pool.query(sql, [phone, upwd], (error, results) => {
    if (error) throw error;
    if(results.length == 0){ //登录失败
      res.send({message:'login failed',code:201});
    } else {                 //登录成功
      res.send({message:'ok',code:200,result:results});
    }
  });
});

//修改密码
server.post('/info',(req,res)=>{
  let obj = req.body
  var i=400 //初始值
	for(var k in obj){
		i++
		//如果属性值为空，提示对应的属性名这项不能为空
		if(!obj[k]){
			return res.send({code:i,msg:k+'不能为空'})
		}
	}
  let sql = 'update lj_user set ? where phone = ?'
  pool.query(sql,[obj,obj.phone],(error,results)=>{
    if(error) throw error
      if(results.affectedRows===0){
        res.send({message:'fail',code:201})
      }else{
        res.send({message:'ok',code:200})
      }
    })
})

//获取列表页内容
server.get('/list',(req,res)=>{
  // 获取客户端传递的page参数
  let page = req.query.page? req.query.page : 1;

  // 存储每页显示的记录数
  let pagesize = 10;

  // 通过公式来计算从第几条记录开始返回
  let offset = (page - 1) * pagesize;

  // 用于存储获取到的总记录数
  let rowcount;
  //SQL查询
  let sql = 'SELECT COUNT(lid) AS count FROM lj_list ORDER BY lid'
  //执行SQL查询
  pool.query(sql,(error,results)=>{
    if(error) throw error
    rowcount = results[0].count

    // 根据总记录数和每页显示的记录数来计算总页数
    let pagecount = Math.ceil(rowcount / pagesize);

    sql = 'SELECT * FROM lj_list ORDER BY lid LIMIT ?,?'
    pool.query(sql,[offset,pagesize],(error,results)=>{
      if(error) throw error
      res.send({message:'ok',code:'200',results:results,pagecount:pagecount})
    })
  })
})


// 指定服务器对象监听的端口号
server.listen(3000, () => {
  console.log('server is running...');
});